package com.it2297.model;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.it2297.main.App;

public class Order {

	private int orderID;
	private Timestamp OrderDate;
	private String customer_id;
	private int payment_id;
	public void setPaymentid(int id){
		this.payment_id = id;
	}
	
	public int getPaymentid(){
		return payment_id;
	}
	public void setCustomerID(String id){
		this.customer_id = id;
	}
	public String getCustomerID(){
		return customer_id;
	}
	public int getOrderID() {
		return orderID;
	}

	public void setOrderID(int orderID) {
		this.orderID = orderID;
	}

	public Timestamp getOrderDate() {
		return OrderDate;
	}

	public void setOrderDate(Timestamp orderDate) {
		OrderDate = orderDate;
	}

	public int CreateOrder(int n, String nric, Timestamp time) {
		
			int orderid =0;
		try {
			App.mySqlConnection.open();
			String sql = "INSERT INTO americano.order_list(date_order, fk_customer_nric, fk_payment_id)";
			sql += "VALUES(?,?,?)";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setTimestamp(1, time);
			ps.setString(2, nric);
			ps.setInt(3, n);

			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		
		try {
			App.mySqlConnection.open();
			String sql = "SELECT LAST_INSERT_ID() AS 'ORDERID'";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				orderid = rs.getInt("ORDERID");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return orderid;
	}
	
	public void deleteOrder(int id){
		try {
			App.mySqlConnection.open();
			String sql = "DELETE  FROM americano.order_list WHERE order_id =?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
	}
	
	public ArrayList<Integer> getAllPayment(String nric){
		ArrayList<Integer> payList = new ArrayList<Integer>();
		try {
			App.mySqlConnection.open();
			String sql = "Select fk_payment_id FROM americano.Payment_View where fk_customer_nric =? and status =?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setString(1, nric);
			ps.setString(2, "unpaid");
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				setOrderID(rs.getInt("fk_payment_id"));
				payList.add(getOrderID());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return payList;
	}
	
	public ArrayList<Order> getUnpaid(int payment_id){
		ArrayList<Order> order = new ArrayList<Order>();
		
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.order_list WHERE fk_payment_id =?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setInt(1, payment_id);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				Order orderItem = new Order();
				orderItem.setAttributes(rs);
				order.add(orderItem);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return order;
		
	}
	public ArrayList<Order> getOrder(String nric){
		
		ArrayList<Order> payList = new ArrayList<Order>();
		try {
			App.mySqlConnection.open();
			String sql = "Select * FROM americano.order_list where fk_customer_nric =?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setString(1, nric);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				Order order = new Order();
				order.setAttributes(rs);
				payList.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return payList;
		
	}
	
	public ArrayList<Order> getAllPaymentDetail(int orderID){
		ArrayList<Order> payList = new ArrayList<Order>();
		int id =0;
		try {
			App.mySqlConnection.open();
			String sql = "Select * FROM americano.Payment_OrderList_View where order_id = ? and status =?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setInt(1, orderID);
			ps.setString(2, "unpaid");
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				Order order = new Order();
				order.setAttributes(rs);
				payList.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return payList;
		
	}
	
	public ArrayList<Order> getAllPaymentHistory(String nric){
		ArrayList<Order> payList = new ArrayList<Order>();
		int id =0;
		try {
			App.mySqlConnection.open();
			String sql = "Select * FROM americano.Payment_OrderList_View where fk_customer_nric =? and status = 'unpaid' ORDER BY order_id";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setString(1, nric);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				Order order = new Order();
				order.setAttributes(rs);
				payList.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return payList;
		
	}

	private void setAttributes(ResultSet rs) {
		try {
		this.setCustomerID(rs.getString("fk_customer_nric"));
		this.setOrderDate(rs.getTimestamp("date_order"));
		this.setOrderID(rs.getInt("order_id"));
		this.setPaymentid(rs.getInt("fk_payment_id"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	

}